package cn.hit.edu.switter;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DatabaseHelper {
	private Connection conn = null;
	private Statement  stat = null;
	public  String 	 status = null; 	
	
	public DatabaseHelper() {
		try {
			getConnection();
			stat = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
		}
	}
	public void closeConnection() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private void getConnection()
		throws SQLException, IOException
	{
		String drivers, url, username, password;
		/*
		Properties      props = new Properties();
		FileInputStream in    = new FileInputStream("database.properties");
		props.load(in);
		in.close();
		
		drivers 	= props.getProperty("jdbc.drivers");
		url 	   	= props.getProperty("jdbc.url");
		username 	= props.getProperty("username");
		password 	= props.getProperty("jdbc.password");
		*/

		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		url = "jdbc:mysql://localhost:3306/switter?useUnicode=true&characterEncoding=gb2312";
		username = "root";
		password = "llflll";
		
		conn =  DriverManager.getConnection(url, username, password);
		
	}
	/*
	public void test() {
		try {
			stat.execute("CREATE TABLE TEST (test varchar(20))");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	*/
	/*Verify whether given email is available*/
	public boolean checkEmail(String email) {
		String query = "SELECT COUNT(UserId) FROM account WHERE Email='" + email+"'";
		ResultSet result;
		try {
			result = stat.executeQuery(query);
			
			if (result.next()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	/*Verify whether account's password can match its email*/
	public int verifyAccount(String email, String password) {
		try {
			String query = "SELECT * FROM account WHERE Email='" + email+"' AND "
							+ "Password='" + password + "'";
			
			ResultSet result =  stat.executeQuery(query);
			if (result.next()) {
				return result.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}
	}

	/*Create a new Account*/
	public int insertAccount(String email, String password, String nickname) {
		String query = "INSERT INTO account (Email, Password, Nickname) VALUES('" + email+ "', '"+ password + "', '" + nickname + "')";
		try {
			ResultSet result = stat.executeQuery(query);
			
			if (result.next()) {
				return result.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}

	}
	
	public Account fetchAccount(int UserId) {
		String query = "SELECT * FROM account where UserId='" + UserId + "'";
		
		ResultSet result;
		try {
			result = stat.executeQuery(query);
			if (result.next()){
				Account acc = new Account();
				acc.setDescription(result.getString("Description"));
				acc.setNickname(result.getString("Nickname"));
				acc.setEmail(result.getString("Email"));
				acc.setPicName(result.getString("PicName"));
				return acc;
			}
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	public void modifyAccount(Account account) {
		
	}
	public boolean modifyAvatar(String picName, int UserId) {
		String query = "UPDATE account SET PicName='" + picName + "' WHERE UserId='" + UserId+ "'";
		try {
			stat.execute(query);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	@SuppressWarnings("deprecation")
	public boolean postMessage(Message msg){
		String query = "INSERT INTO message (UserId, Details, PostTime) VALUES ('" +
						msg.getUserId() + "', '" + msg.getMsg() + "', '" + msg.getPostTime().toLocaleString()+ "')";
		
		try {
			stat.execute(query);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	
	public ArrayList<Message> fetchMessages(int userId, int num) {
		String query = "SELECT * FROM messa" +
				"ge where UserId='" + userId + "'";
		
		ArrayList<Message> list = new ArrayList<Message>();
		ResultSet result;
		try {
			result = stat.executeQuery(query);
			while (result.next()){
				Message msg = new Message();
				msg.setMsg(result.getString("Details"));
				msg.setPostTime(result.getDate("PostTime"));
				msg.setUserId(result.getInt("UserId"));
				System.out.println(msg.getMsg());
				list.add(msg);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}
	
	public ArrayList<Message> fetchMessagesAfter(int userId, int timestamp, int num) {
		String query = "SELECT * FROM message where UserId='" + userId + "', PostTime>'" + timestamp + "'";
		Message msg = new Message();
		ArrayList<Message> list = new ArrayList<Message>();
		ResultSet result;
		try {
			result = stat.executeQuery(query);
			while (result.next()){
				msg.setMsg(result.getString("Details"));
				msg.setPostTime(result.getDate("PostTime"));
				msg.setUserId(result.getInt("UserId"));
				list.add(msg);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		
	}
	
	public ArrayList<Integer> fetchFriends(int userId) {
		String query = "SELECT * FROM friendlist where UserId='" + userId + "'";
		ArrayList<Integer> list = new ArrayList<Integer>();
		
		ResultSet result;
		try {
			result = stat.executeQuery(query);
			while (result.next()){
				Integer i = (Integer) result.getInt("FriendId");
				list.add(i);
				System.out.println(i);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean IsFriend(int fid, int uid) {
		String query = "SELECT * FROM friendlist WHERE UserId='" + uid + "', '" + fid+ "'";
		
		ResultSet result;
		try {
			result = stat.executeQuery(query);
			if (result.next()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean makeFriend(int fid, int uid) {
		String query = "INSERT INTO friendlist (FriendId, UserId) VALUES(" + fid+ ", "+uid +")";

		try {
			stat.execute(query);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	public ArrayList<Message> fetchAllUserMessages() {
		String query = "SELECT * FROM message";
		ArrayList<Message> list = new ArrayList<Message>(0);
		
		ResultSet result;
		try {
			result = stat.executeQuery(query);
			while (result.next()) {
				Message msg = new Message();
				msg.setMsg(result.getString("Details"));
				msg.setPostTime(result.getDate("PostTime"));
				msg.setUserId(result.getInt("UserId"));
				System.out.println(msg.getMsg());
				list.add(msg);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean addFriend(int fId, int uId) {
		String query = "INSERT INTO friendlist (FriendId, UserId) VALUES('" + fId + "', '" + uId + "')";
		try {
			stat.execute(query);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	/*
	public int deleteMessage(Message msg) {
		
	}
	*/
	
	
	@SuppressWarnings("deprecation")
	public static void main(String[] args) {
		DatabaseHelper db = new DatabaseHelper();
		db.fetchFriends(7);
	}
}
